package cn.sykj.car.utils.excel;

import cn.sykj.car.utils.ReflectUtils;
import cn.sykj.car.utils.Utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 根据Bean上的Excel注解写入到Excel
 * @author Stan 2015/9/16 ReWrite
 */
@Slf4j
public class ExcelUtils {
	public static final String EXCEL_2003_EXT = ".xls"; // 2003- 版本的excel
	public static final String EXCEL_2007_EXT = ".xlsx"; // 2007+ 版本的excel



	/**
	 * 获得Workbook对象
	 * 
	 * @param list
	 *            数据集合
	 * @return Workbook
	 * @throws Exception
	 */
	public static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf) throws IllegalAccessException {
		// 创建工作簿
		Workbook wb = new SXSSFWorkbook();

		if (list == null || list.size() == 0)
			return wb;

		// 创建一个工作表sheet
		Sheet sheet = wb.createSheet();
		// 申明行
		Row row = sheet.createRow(0);
		// 申明单元格
		Cell cell = null;

		CreationHelper createHelper = wb.getCreationHelper();

		// 注解列表
		Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(list.get(0).getClass());

		List<Field> fList = new ArrayList<>();
		for(Field field : fields){
			Excel ef = field.getAnnotation(Excel.class);
			if(ef != null){
				fList.add(field);
			}
		}

		XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();
		titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		// 设置前景色
		titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(159, 213, 183)));
		titleStyle.setAlignment(HorizontalAlignment.CENTER);

		Font font = wb.createFont();
		font.setColor(IndexedColors.BROWN.index);
		font.setBold(true);
		// 设置字体
		titleStyle.setFont(font);

		int columnIndex = 0;
		Excel excel = null;
		/************************创建列头名称***************/
		for (Field field : fList) {
			field.setAccessible(true);
			excel = field.getAnnotation(Excel.class);
			if (excel == null || excel.skip() == true) {
				continue;
			}
			// 根据指定列的顺序获取列号
			if(StringUtils.isNotBlank(excel.sort())){
				columnIndex = getExcelCol(excel.sort());
			}

			// 列宽注意乘256
			sheet.setColumnWidth(columnIndex, excel.width() * 256);
			// 创建列,写入标题
			cell = row.createCell(columnIndex);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(excel.name());

			columnIndex++;
		}

		/**************************创建内容列******************/
		int rowIndex = 1;

		CellStyle cs = wb.createCellStyle();
		
		// 计算合计
		Map<String,BigDecimal> sumMap = new HashMap<>();

		for (T t : list) {
			row = sheet.createRow(rowIndex);
			columnIndex = 0;
			Object o = null;
			for (Field field : fields) {

				field.setAccessible(true);

				// 忽略标记skip的字段
				excel = field.getAnnotation(Excel.class);
				if (excel == null || excel.skip() == true) {
					continue;
				}
				// 数据
				// 按指定的顺序获得列号
				if(StringUtils.isNotBlank(excel.sort())){
					columnIndex = getExcelCol(excel.sort());
				}
				cell = row.createCell(columnIndex);

				o = field.get(t);
				// 如果数据为空，跳过
				if (o == null) {
					cell.setCellValue("");
					columnIndex++;
					continue;
				}

				// 处理日期类型
				if (o instanceof Date) {
					// excel.dateFormat()获取注解的日期格式，默认yyyy-MM-dd HH:mm:ss
					cs.setDataFormat(createHelper.createDataFormat().getFormat(excel.dateFormat()));
					cell.setCellStyle(cs);
					cell.setCellValue((Date) field.get(t));
				} else if (o instanceof Double || o instanceof Float) {// 浮点数
					cell.setCellValue(field.get(t).toString());
					if(Utils.isEmpty(sumMap.get(excel.name()))){
						sumMap.put(excel.name(),new BigDecimal(field.get(t).toString()));
					}else{
						sumMap.put(excel.name(),new BigDecimal(field.get(t).toString()).add(sumMap.get(excel.name())));
					}

					if(excel.percent()){
						if (excel.precision() != -1) {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).multiply(new BigDecimal(100)).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString().concat("%"));
						}
						else {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).multiply(new BigDecimal(100)).toString().concat("%"));
						}
					}
					else {
						if (excel.precision() != -1) {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString());
						}
					}
				} else if (o instanceof BigDecimal) {// BigDecimal
					cell.setCellValue((field.get(t).toString()));
					if(Utils.isEmpty(sumMap.get(excel.name()))){
						sumMap.put(excel.name(),new BigDecimal(field.get(t).toString()));						
					}else{
						sumMap.put(excel.name(),new BigDecimal(field.get(t).toString()).add(sumMap.get(excel.name())));
					}

					if(excel.percent()){
						if (excel.precision() != -1) {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).multiply(new BigDecimal(100)).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString().concat("%"));
						}
						else {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).multiply(new BigDecimal(100)).toString().concat("%"));
						}
					}
					else {
						if (excel.precision() != -1) {
							cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString());
						}
					}
				} else if (o instanceof Boolean) {// 布尔类型
					Boolean bool = (Boolean) field.get(t);
					if (edf == null) {
						cell.setCellValue(bool);
					} else {
						Map<String, String> map = edf.get(field.getName());
						if (map == null) {
							cell.setCellValue(bool);
						} else {
							cell.setCellValue(map.get(bool.toString().toLowerCase()));
						}
					}

				} else if (o instanceof Integer) {// 整型

					Integer intValue = (Integer) field.get(t);

					if (edf == null) {
						cell.setCellValue(intValue);
					} else {
						Map<String, String> map = edf.get(field.getName());
						if (map == null) {
							cell.setCellValue(intValue);
						} else {
							cell.setCellValue(map.get(intValue.toString()));
						}
					}

					if(Utils.isEmpty(sumMap.get(excel.name()))){
						sumMap.put(excel.name(),new BigDecimal(intValue).divide(new BigDecimal(100),BigDecimal.ROUND_HALF_EVEN).setScale(2));
					}else{
						sumMap.put(excel.name(),new BigDecimal(intValue).divide(new BigDecimal(100),BigDecimal.ROUND_HALF_EVEN).setScale(2).add(sumMap.get(excel.name())));
					}

					if(excel.percent()){
						if (excel.precision() == -2) {
//							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString().concat("%"));
							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).toString());
						}
						else {

						}
					}
					else {
						if (excel.precision() == -2) {
//							cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString());
							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).toString());
						}
					}
				}else if (o instanceof Long) {// 长整型

					Long intValue = (Long) field.get(t);

					if (edf == null) {
						cell.setCellValue(intValue);
					} else {
						Map<String, String> map = edf.get(field.getName());
						if (map == null) {
							cell.setCellValue(intValue);
						} else {
							cell.setCellValue(map.get(intValue.toString()));
						}
					}

					if(Utils.isEmpty(sumMap.get(excel.name()))){
						sumMap.put(excel.name(),new BigDecimal(intValue).divide(new BigDecimal(100),BigDecimal.ROUND_HALF_EVEN).setScale(2));
					}else{
						sumMap.put(excel.name(),new BigDecimal(intValue).divide(new BigDecimal(100),BigDecimal.ROUND_HALF_EVEN).setScale(2).add(sumMap.get(excel.name())));
					}

					if(excel.percent()){
						if (excel.precision() == -2) {
//							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString().concat("%"));
							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).toString());
						}
						else {

						}
					}
					else {
						if (excel.precision() == -2) {
//							cell.setCellValue(new BigDecimal(field.get(t).toString()).setScale(excel.precision(), excel.round() == true ? BigDecimal.ROUND_HALF_EVEN : BigDecimal.ROUND_FLOOR).toString());
							cell.setCellValue(new BigDecimal(intValue).divide(new BigDecimal(100)).toString());
						}
					}
				} else {
					cell.setCellValue(field.get(t).toString());
				}

				columnIndex++;
			}

			rowIndex++;
		}

		return wb;
	}

	/**
	 * 将数据写入到EXCEL文档
	 * 
	 * @param list
	 *            数据集合
	 * @param edf
	 *            数据格式化，比如有些数字代表的状态，像是0:女，1：男，或者0：正常，1：锁定，变成可读的文字
	 *            该字段仅仅针对Boolean,Integer两种类型作处理
	 * @param filePath
	 *            文件路径
	 * @throws Exception
	 */
	public static <T> void writeExcelToFile(List<T> list, ExcelDataFormatter edf, String filePath) throws IOException, IllegalAccessException {
		// 创建并获取工作簿对象
		Workbook wb = getWorkBook(list, edf);
		// 写入到文件
		FileOutputStream out = new FileOutputStream(filePath);
		wb.write(out);
		out.close();
	}

	/**
	 * 将数据写入到EXCEL文档
	 *
	 * @param list
	 *            数据集合
	 * @param edf
	 *            数据格式化，比如有些数字代表的状态，像是0:女，1：男，或者0：正常，1：锁定，变成可读的文字
	 *            该字段仅仅针对Boolean,Integer两种类型作处理
	 * @param out
	 *            文件路径
	 * @throws Exception
	 */
	public static <T> void writeExcelToStream(OutputStream out, List<T> list, ExcelDataFormatter edf) throws IOException, IllegalAccessException {
		// 创建并获取工作簿对象
		Workbook wb = getWorkBook(list, edf);
		wb.write(out);
		out.flush();
		out.close();
		wb.close();
	}

	/**
	 * 获得Excel数据
	 *
	 * @param file
	 *            Excel文件
	 * @return Workbook
	 * @throws Exception
	 */
	public static List<Map> getExcelData(File file) throws IllegalAccessException, IOException {
		InputStream inputStream = new FileInputStream(file);
		String fileName = file.getName();
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		Workbook workbook = null;
		if (EXCEL_2003_EXT.equals(fileType)) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (EXCEL_2007_EXT.equals(fileType)) {
			workbook = new XSSFWorkbook(inputStream);
		}
		Sheet sheet = workbook.getSheetAt(0);
		if (sheet == null) {
			return null;
		}
		List list = new ArrayList();
		int lastRowIndex = sheet.getLastRowNum();
		for(int rIndex = 0; rIndex < lastRowIndex; rIndex++) {   //遍历行
			Map map = new HashMap();
			Row row = sheet.getRow(rIndex);
			if (row != null) {
				for (int cIndex = 0; cIndex < 3; cIndex++) {   //遍历列
					Cell cell = row.getCell(cIndex);
					map.put(cIndex, getCellValue(cell));
				}
				list.add(map);
			}
		}
		return list;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		Object value = null;
		DecimalFormat df = new DecimalFormat("#.####"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("#.####"); // 格式化数字

		switch (cell.getCellType()) {
			case STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			case NUMERIC:
				if ("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				} else {
					value = df2.format(cell.getNumericCellValue());
				}
				break;
			case BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case BLANK:
				value = "";
				break;
			default:
				break;
		}
		return value;
	}

	public static <T> void writeExcelToStreamTest(HttpServletResponse response,
												  OutputStream out, List<T> list, ExcelDataFormatter edf) throws IOException, IllegalAccessException {
		// 创建并获取工作簿对象
		Workbook wb = getWorkBook(list, edf);
//		wb.write(out);
//		out.flush();
//		out.close();
//		wb.close();
		FileOutputStream fileOut;
            try {
                fileOut = new FileOutputStream("d:/shishi.xls");
				wb.write(fileOut);
                fileOut.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
	}

	public static int getExcelCol(String col) {
		col = col.toUpperCase();
		// 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
		int count = -1;
		char[] cs = col.toCharArray();
		for (int i = 0; i < cs.length; i++) {
			count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
		}
		return count;
	}
}
